package cn.cjy.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.cjy.dao.CatalogDao;
import cn.cjy.model.Catalog;
import cn.cjy.utils.DBUtil;
import cn.cjy.utils.MysqlUtil;

public class CatalogDaoImpl implements CatalogDao{

	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	List<Catalog> ca = null;
	int n = 0;
	@Override
	public List<Catalog> all() {
		List<Catalog> catalogs=new ArrayList<Catalog>();
		try {
			String sql="select * from catalog where 1=1";
			ResultSet rs=MysqlUtil.excuteMysqlSql(sql);
			Catalog catalog=null;
			while(rs.next()) {
				catalog=new Catalog();
				catalog.setId(rs.getInt("id"));
				catalog.setTitle(rs.getString("title"));
				catalog.setParent_id(rs.getInt("parent_id"));
				catalogs.add(catalog);
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return catalogs;
	}

	@Override
	public Catalog one(String id) {
		Catalog catalog=null;
		try {
			conn = DBUtil.getConnection();
			String sql = "select * from catalog where id=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,id);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				catalog=new Catalog();
				catalog.setTitle(rs.getString("title"));
				catalog.setParent_id(rs.getInt("parent_id"));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return catalog;
	}

	@Override
	public Integer update(Catalog catalog) {
		try {
			conn = DBUtil.getConnection();
			String sql = "update catalog set title=?,parent_id=? where title=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(3,catalog.getTitle());	
			pstmt.setString(1,catalog.getTitle());		
			pstmt.setInt(2, catalog.getParent_id());

			n = pstmt.executeUpdate();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("n=" + n);
		return n;
	}

	@Override
	public Integer add(Catalog catalog) {
		String sql = " insert into catalog (title,parent_id) values ('" + catalog.getTitle() + "','" + catalog.getParent_id() + "')";

		Integer row = MysqlUtil.excuteMysqlUpdate(sql);

		return row;
	}

	@Override
	public Integer delete(String id) {
		String sql="delete from catalog where id="+id;
		Integer row = MysqlUtil.excuteMysqlUpdate(sql);

		return row;
	}

	@Override
	public List<Catalog> selectCatalogById(String key) {
		
		try {
			conn = DBUtil.getConnection();
			String sql = "select * from catalog where id=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,key);
			rs = pstmt.executeQuery();
			ca=new ArrayList<Catalog>();
			while(rs.next()) {
				Catalog catalog=new Catalog();
				catalog.setId(rs.getInt("id"));
				catalog.setTitle(rs.getString("title"));
				catalog.setParent_id(rs.getInt("parent_id"));
				ca.add(catalog);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ca;
	}

}
